package com.dob.dao;

import com.dob.page.PageUtils;
import com.dob.page.Paging;
import com.dob.reponsitory.User;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.map.HashedMap;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Repository
public class UserDao {

    private static final String tbName = "tb_user";

    private static final String colSQL = "id,nick_name,real_name,user_name,pwd,avatar,gender,birth,id_no,mobile,email,auth,status,is_del,create_time,update_time";

    private static final String insertSQL = "INSERT INTO " + tbName + "(nick_name,real_name,user_name,pwd,avatar,gender,birth,id_no,mobile,email,auth,status,is_del,create_time,update_time) VALUES(:nickName,:realName,:userName,:pwd,:avatar,:gender,:birth,:idNo,:mobile,:email,:auth,:status,:isDel,:createTime,:updateTime)";

    private static final String selectSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE id=:id";

    private static final String isDelSQL = "UPDATE " + tbName + " SET is_del=:isDel,update_time=:updateTime WHERE id=:id";

    private static final String updatePwdByIdSQL = "UPDATE " + tbName + " SET pwd=:pwd WHERE id=:id";

    private static final String selectByUserNameSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE is_del =:isDel  AND user_name=:userName";

    private static final String selectAllUserSQL = "SELECT " + colSQL + " FROM " + tbName + " WHERE is_del =:isDel";

    private static final String isEnableSQL = "UPDATE " + tbName + " SET status=:status WHERE id =:id";

    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public int save(User user) {
        SqlParameterSource ps = new BeanPropertySqlParameterSource(user);
        KeyHolder keyHolder = new GeneratedKeyHolder();
        namedParameterJdbcTemplate.update(insertSQL, ps, keyHolder);
        return keyHolder.getKey().intValue();
    }

    public int delById(Integer id) {
        Map<String, Object> param = new HashMap<>();
        param.put("id", id);
        param.put("updateTime", new Date());
        param.put("isDel", 0);
        return namedParameterJdbcTemplate.update(isDelSQL, param);
    }

    public Integer resetPassword(Integer id, String pwd) {
        Map<String, Object> param = new HashedMap();
        param.put("id", id);
        param.put("pwd", pwd);
        return namedParameterJdbcTemplate.update(updatePwdByIdSQL, param);
    }

    public Integer isEnable(Integer id, Integer status) {
        Map<String, Object> param = new HashedMap();
        param.put("id", id);
        param.put("status", status);
        return namedParameterJdbcTemplate.update(isEnableSQL, param);
    }


    public User findById(Integer id) {
        Map<String, Object> param = new HashedMap();
        param.put("id", id);
        List<User> list = namedParameterJdbcTemplate.query(selectSQL, param, new Mapper());
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    public User findByUserName(String userName, int isDel) {
        Map<String, Object> param = new HashedMap();
        param.put("userName", userName);
        param.put("isDel", isDel);
        List<User> list = namedParameterJdbcTemplate.query(selectByUserNameSQL, param, new Mapper());
        return CollectionUtils.isEmpty(list) ? null : list.get(0);
    }

    public Integer countByPage(Paging page, User user) {
        StringBuilder sb = new StringBuilder("SELECT COUNT(id) FROM " + tbName + " WHERE 1=1 ");
        Map<String, Object> param = new HashedMap();
        where(sb, param, user, page);
        return namedParameterJdbcTemplate.queryForObject(sb.toString(), param, Integer.class);
    }

    public List<User> findByPage(Paging page, User user) {
        StringBuilder sb = new StringBuilder("SELECT " + colSQL + " FROM " + tbName + " WHERE 1=1 ");
        Map<String, Object> param = new HashedMap();
        where(sb, param, user, page);
        PageUtils.page(page, param, sb);
        return namedParameterJdbcTemplate.query(sb.toString(), param, new Mapper());
    }


    private void where(StringBuilder sb, Map<String, Object> param, User user, Paging page) {
        if (user.getNickName() != null) {
            param.put("nickName", user.getNickName());
            sb.append(" AND nick_name LIKE :nickName");
        }
        if (user.getRealName() != null) {
            param.put("realName", user.getRealName());
            sb.append(" AND real_name LIKE :realName");
        }
        if (user.getUserName() != null) {
            param.put("userName", user.getUserName());
            sb.append(" AND user_name=:userName");
        }

        if (user.getGender() != null) {
            param.put("gender", user.getGender());
            sb.append(" AND gender=:gender");
        }
        if (user.getIdNo() != null) {
            param.put("idNo", user.getIdNo());
            sb.append(" AND id_no=:idNo");
        }
        if (user.getMobile() != null) {
            param.put("mobile", user.getMobile());
            sb.append(" AND mobile=:mobile");
        }

        if (user.getAuth() != null) {
            param.put("auth", user.getAuth());
            sb.append(" AND auth=:auth");
        }
        if (user.getStatus() != null) {
            param.put("status", user.getStatus());
            sb.append(" AND status=:status");
        }
        if (user.getIsDel() != null) {
            param.put("isDel", user.getIsDel());
            sb.append(" AND is_del=:isDel");
        }
        PageUtils.page(page, param, sb, false);

    }

    public void update(User user) {
        StringBuilder sb = new StringBuilder("UPDATE " + tbName + " SET update_time=:updateTime");
        Map<String, Object> param = new HashedMap();
        param.put("updateTime", new Date());
        if (StringUtils.isNotBlank(user.getAvatar())) {
            sb.append(",avatar=:avatar");
            param.put("avatar", user.getAvatar());
        }
        if (StringUtils.isNotBlank(user.getNickName())) {
            sb.append(",nick_name=:nickName");
            param.put("nickName", user.getNickName());
        }

        if (StringUtils.isNotBlank(user.getUserName())) {
            sb.append(",user_name=:userName");
            param.put("userName", user.getUserName());
        }

        if (StringUtils.isNotBlank(user.getRealName())) {
            sb.append(",real_name=:realName");
            param.put("realName", user.getRealName());
        }
        sb.append(",mobile=:mobile");
        param.put("mobile", user.getMobile());

        sb.append(" WHERE id =:id");
        param.put("id", user.getId());
        namedParameterJdbcTemplate.update(sb.toString(), param);
    }

    class Mapper implements RowMapper<User> {
        @Override
        public User mapRow(ResultSet rs, int i) throws SQLException {
            User item = new User();
            item.setId(rs.getInt("id"));
            item.setNickName(rs.getString("nick_name"));
            item.setRealName(rs.getString("real_name"));
            item.setUserName(rs.getString("user_name"));
            item.setPwd(rs.getString("pwd"));
            item.setAvatar(rs.getString("avatar"));
            item.setGender(rs.getInt("gender"));
            item.setBirth(rs.getTimestamp("birth"));
            item.setIdNo(rs.getString("id_no"));
            item.setMobile(rs.getString("mobile"));
            item.setEmail(rs.getString("email"));
            item.setAuth(rs.getInt("auth"));
            item.setStatus(rs.getInt("status"));
            item.setIsDel(rs.getInt("is_del"));
            item.setCreateTime(rs.getTimestamp("create_time"));
            item.setUpdateTime(rs.getTimestamp("update_time"));
            return item;
        }
    }

}
